package com.sq.partner.manager.dao.impl;	
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.log4j.Logger;
import org.mybatis.spring.SqlSessionTemplate;

import com.sq.partner.manager.common.Constants;
import com.sq.partner.manager.dao.BaseDao;
import com.sq.partner.manager.util.DBUtil;
import com.sq.partner.manager.util.LogUtil;
import com.sq.partner.manager.util.Page;

/**
 * 基础dao类
 * 用于封装一些基本的操作
 * @author fshitd04
 * May 20, 2013
 */
public class BaseDaoImpl implements BaseDao{
	private static Logger logger = LogUtil.getLog(BaseDaoImpl.class);
	private SqlSessionTemplate session; 
	private DataSource dataSource;
	
	
	@Resource(name="sqlSession")
	public void setSession(SqlSessionTemplate session) {
		this.session = session;
	}
	
	@Resource(name="dataSource")
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
	
	public SqlSessionTemplate getSession() {
		return session;
	}
	
	/**
	 * 执行存储过程/函数
	 * 返回结果请从para中取
	 * @param <T>
	 * @param statementName  Sql配置名
	 * @param para 参数,根据配置文件需要，结果也从中获取
	 * @return
	 * @throws Exception
	 */
	public <T>  void call(String statementName,Map para) throws SQLException
	{	
		logger.debug("执行mybatis操作:"+statementName);
		logger.debug("参数:"+para);
		session.selectOne(statementName,para);
	}
	
	/**
	 * 查询返回对像
	 * @param <T>
	 * @param statementName  Sql配置名
	 * @param para 参数,根据配置文件需要，可以传Bean,Map,简单类型等
	 * @return
	 * @throws Exception
	 */
	public <T>  Object query(String statementName,T para) throws SQLException
	{	
		logger.debug("执行mybatis操作:"+statementName);
		logger.debug("参数:"+para);
		return session.selectOne(statementName, para);
	}
	
	/**
	 * 查询,返回List
	 * @param <T>
	 * @param statementName Sql配置名
	 * @param para 参数,根据配置文件需要，可以传Bean,Map,简单类型等
	 * @return
	 * @throws Exception
	 */
	public <T>  List queryList(String statementName,T para) throws SQLException
	{
		logger.debug("执行mybatis操作:"+statementName);
		logger.debug("参数:"+para);
		return session.selectList(statementName, para);
	}
	
	public Page queryPage(int current,String clumnNames,String tableNames,String whereCluse,String orderByCluse,Object[] params) throws SQLException{
		return queryPage(current,Constants.PAGE_SIZE,clumnNames,tableNames,whereCluse,orderByCluse,params);
	}
	
	
	/**
	 * 使用sql查询返回对像 如果有多条，则报出导常
	 * @param sql sql
	 * @param params 参数列表用于按顺序替换?
	 * @return 查询结果字段对应的map，其key不分大小写 没有结果返回null
	 * @throws Exception
	 */
	public Map query(String sql,Object[] params) throws SQLException{
		List<Map> l = queryList(sql,params);
		if(l == null || l.isEmpty()){
			return null;
		}else if(l.size() > 1){
			throw new SQLException("记录超过一条");
		}
		return l.get(0);
	}
	
	/**
	 * @param sql sql
	 * @param params 参数列表用于按顺序替换?
	 * @return List<Map> map查询结果与字段对应的，其key不分大小写 没有结果返回null
	 * @throws Exception
	 */
	public List<Map> queryList(String sql,Object[] params) throws SQLException{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try{
			conn = getConnection();
			logger.debug("执行SQL："+sql);
			pstmt = conn.prepareStatement(sql);
			if(params != null){
				for(int i = 0; i < params.length; i++){
					logger.debug("params:"+params[i]);
					pstmt.setObject(i+1,params[i]);
				}
			}
			
			rs = pstmt.executeQuery();
			return DBUtil.transRs2List(rs);
		}finally{
			if(rs!=null){
				try{
					rs.close();
				}catch (Exception e) {
					logger.error("关闭ResultSet失败",e);
				}
			}
			if(pstmt!=null){
				try{
					pstmt.close();
				}catch (Exception e) {
					logger.error("关闭PreparedStatement失败",e);
				}
			}
			
			close(conn);
		}
	}
	
	/**
	 * 查询,返回page对像
	 * 描述性sql:select clumnNames from tableNames where whereCluse orderby orderByCluse,使用size和current进行分页
	 * @param statementName Sql配置名
	 * @param para 参数,根据配置文件需要，可以传Bean,Map,简单类型等
	 * @return
	 * @throws Exception
	 */
	public Page queryPage(int current,int size,String clumnNames,String tableNames,String whereCluse,String orderByCluse,Object[] params) throws SQLException
	{	
		Connection conn = null;
		PreparedStatement pstmt = null;
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try{
			conn = getConnection();
			StringBuffer countSql = new StringBuffer();
			countSql.append("select count(*) from ")
			.append(tableNames)
			.append(" where ")
			.append(whereCluse);
			logger.debug("执行SQL：" + countSql);
			pstmt = conn.prepareStatement(countSql.toString());
			if(params != null){
				for(int i = 0; i < params.length; i++){
					logger.debug("params:"+params[i]);
					pstmt.setObject(i+1,params[i]);
				}
			}
			
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);
			
			//page
			Page p = new Page(current,total,size);
			
			StringBuffer dataSql =  new StringBuffer();
			dataSql.append("Select * From (")
			.append("Select Rownum inner_Rownum, inner_table.* From (")
			.append("select ")
			.append(clumnNames)
			.append(" from ")
			.append(tableNames);
			if(whereCluse != null && !"".equals(whereCluse)){
				dataSql.append(" where ")
				.append(whereCluse);
			}
			
			if(orderByCluse != null && !"".equals(orderByCluse)){
				dataSql.append(" order by  ")
				.append(orderByCluse);
			}
			
			dataSql.append(" ) inner_table  Where Rownum<= ")
			.append(p.getLastRecodeIndex())
			.append(" ) Where inner_Rownum >= ")
			.append(p.getFirstRecodeIndex());
			
			logger.debug("执行SQL："+dataSql);
			pstmt1 = conn.prepareStatement(dataSql.toString());
			if(params != null){
				for(int i = 0; i < params.length; i++){
					logger.debug("params:"+params[i]);
					pstmt1.setObject(i+1,params[i]);
				}
			}
			rs = pstmt1.executeQuery();
			List l = DBUtil.transRs2List(rs);
			p.setData(l);
			
			return p;
		}finally{
			if(rs!=null){
				try{
					rs.close();
				}catch (Exception e) {
					logger.error("关闭ResultSet失败",e);
				}
			}
			if(pstmt!=null){
				try{
					pstmt.close();
				}catch (Exception e) {
					logger.error("关闭PreparedStatement失败",e);
				}
			}
			if(pstmt1!=null){
				try{
					pstmt1.close();
				}catch (Exception e) {
					logger.error("关闭PreparedStatement失败",e);
				}
			}
			
			close(conn);
		}
		
	}
	
	
	/**
	 * 将fromColumn的值转换成toColumn的值
	 * @param tableName
	 * @param fromColumn
	 * @param toColumn
	 * @param fromValue
	 * @return
	 * @throws SQLException
	 */
	public String transColumn(String tableName,String fromColumn,String toColumn,Object fromValue) throws SQLException{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try{
			StringBuffer sb = new StringBuffer();
			sb.append("select ")
			.append(toColumn)
			.append(" from ")
			.append(tableName)
			.append(" where ")
			.append(fromColumn)
			.append(" =  ?");
			
			conn = getConnection();
			logger.debug("执行SQL：" + sb);
			logger.debug("params:" + fromValue);
			pstmt = conn.prepareStatement(sb.toString());
			pstmt.setObject(1,fromValue);
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				return rs.getString(1);
			}
			
			return null;
			
		}finally{
			if(rs!=null){
				try{
					rs.close();
				}catch (Exception e) {
					logger.error("关闭ResultSet失败",e);
				}
			}
			
			if(pstmt!=null){
				try{
					pstmt.close();
				}catch (Exception e) {
					logger.error("关闭PreparedStatement失败",e);
				}
			}
			
			close(conn);
		}
	}
	
	/**
	 * 添加
	 * @param <T>
	 * @param statementName Sql配置名
	 * @param entity 操作对像
	 * @return
	 * @throws Exception
	 */
	public <T>  boolean  add(String statementName,T entity) throws Exception{	
		logger.debug("执行mybatis操作:"+statementName);
		logger.debug("参数:"+entity);
		boolean flag=false;
		session.insert(statementName, entity);
		flag=true;
		return flag;
	}
	
	public <T> Object addAndReturnPrimaryKey(String statementName,T entity) throws Exception{	
		logger.debug("执行mybatis操作:"+statementName);
		logger.debug("参数:"+entity);
		return session.insert(statementName, entity);
	}
	
	/**
	 * 添加并自动更新fcd fcu lcd lcu 如果有的对应set方法
	 * @param <T>
	 * @param statementName Sql配置名
	 * @param entity 操作对像
	 * @param user 用户名
	 * @return
	 * @throws Exception
	 */
	public <T>  boolean  add(String statementName,T entity,String user) throws Exception{	
		logger.debug("执行mybatis操作:"+statementName);
		logger.debug("参数:"+entity);
		try{
			setUserAndDate(entity,user,0);
		}catch (Exception e) {
			logger.error("自动设置日期时间失败",e);
		}
		boolean flag=false;
		session.insert(statementName, entity);
		flag=true;
		return flag;
	}
	
	/**
	 * 更新
	 * @param <T>
	 * @param statementName Sql配置名
	 * @param entity 操作对像
	 * @return
	 * @throws Exception
	 */
	public <T>  boolean  update(String statementName,T entity) throws Exception{
		logger.debug("执行mybatis操作:"+statementName);
		logger.debug("参数:"+entity);
		boolean flag=false;
		
		int updateNum=session.update(statementName, entity);
		if(updateNum>0){
			flag=true;
		}
		return flag;
	}
	
	/**
	 * 更新并自动更新lcd lcu 如果有的对应set方法
	 * @param <T>
	 * @param statementName Sql配置名
	 * @param entity 操作对像
	 * @param user 用户名
	 * @return
	 * @throws Exception
	 */
	public <T>  boolean  update(String statementName,T entity,String user) throws Exception{
		logger.debug("执行mybatis操作:"+statementName);
		logger.debug("参数:"+entity);
		boolean flag=false;
		try{
			setUserAndDate(entity,user,0);
		}catch (Exception e) {
			logger.error("自动设置日期时间失败",e);
		}
		int updateNum=session.update(statementName, entity);
		if(updateNum>0){
			flag=true;
		}
		return flag;
	}
	/**
	 * 
	 * @param <T>
	 * @param entity 目标对像
	 * @param user 要设的user
	 * @param flag 0 add 1 update
	 */
	private <T> void setUserAndDate(T entity,String user,int flag) throws Exception{
		Method[] ms = entity.getClass().getMethods();
		for(Method m : ms){
			String name = m.getName();
			if("setLcu".equals(name) || (flag == 0 && "setFcu".equals(name))){
				Class[] c = m.getParameterTypes();
				if(c != null && c.length == 1 && c[0].equals(String.class)){
					 m.invoke(entity,new Object[]{user});
					 logger.debug("已自动为对像"+ entity + "执行"+name+"方法，参数为:"+user);
				 }
			}else if("setLcd".equals(name) || (flag == 0 && "setFcd".equals(name))){
				Class[] c = m.getParameterTypes();
				if(c != null && c.length == 1){
					Object date = null;
					if(c[0].equals(Date.class)){
						date= new Date();
					}else if(c[0].equals(java.sql.Date.class)){
						date= new java.sql.Date(System.currentTimeMillis());
					}else if(c[0].equals(Timestamp.class)){
						date= new Timestamp(System.currentTimeMillis());
					}
					if(date != null){
						m.invoke(entity,new Object[]{date});
						logger.debug("已自动为对像"+ entity + "执行"+name+"方法，时间为当前时间");
					}
				 }
			}
		}
	}
	
	/**
	 * 更新
	 * @param statementName Sql配置名
	 * @param entity 操作对像,以statementName要求为准
	 * @return
	 * @throws Exception
	 */
	public <T>  boolean  delete(String statementName,T entity) throws Exception{
		logger.debug("执行mybatis操作:"+statementName);
		logger.debug("参数:"+entity);
		boolean flag=false;
		int deleteNum=session.delete(statementName, entity);
		if(deleteNum>0){
			flag=true;
		}
		return flag;
	}
	
    /**
     * 批量删除
     * @param statementName    sql文件ID
     * @param list             参数集合
     */
	@SuppressWarnings("unchecked")
    public void batchDelete(final String statementName, final List list) throws Exception{
		logger.debug("执行mybatis批量操作:"+statementName);
        //TODO 暂未实现
     }
    /**
     * 批量修改
     * @param statementName  sql文件ID
     * @param list           参数集合
     */
    @SuppressWarnings("unchecked")
    public void batchUpdate(final String statementName, final List list) throws Exception{
    	logger.debug("执行mybatis批量操作:"+statementName);

    	logger.info("插入记录数:"+list.size());
    	long time1 = System.currentTimeMillis();
    	SqlSession sessionBatch = null;
    	try{
    		sessionBatch = session.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);
    		for(Object o : list){
    			sessionBatch.update(statementName, o);
    		}
    		
    		sessionBatch.commit();
    		sessionBatch.clearCache();
    		logger.info("批量修改成功，操作执行时间："+ (System.currentTimeMillis() -time1));
    	}catch(Exception e){
    		try{
    			sessionBatch.rollback();
    		}catch(Exception ex){
    			logger.warn(ex);
    		}
    		throw e;
    	}finally{
    		try{
    			sessionBatch.close();
    		}catch(Exception ex){
    			logger.warn(ex);
    		}
    	}
     
     }
    
    /**
     * 批量添加
     * @param statementName sql文件ID
     * @param list   参数集合
     */
    @SuppressWarnings("unchecked")
    public void batchInsert(final String statementName, final List list) throws Exception{
    	logger.debug("执行mybatis批量操作:"+statementName);
    	logger.info("插入记录数:"+list.size());
    	long time1 = System.currentTimeMillis();
    	SqlSession sessionBatch = null;
    	try{
    		sessionBatch = session.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);
    		for(Object o : list){
    			sessionBatch.insert(statementName, o);
    		}
    		
    		sessionBatch.commit();
    		sessionBatch.clearCache();
    		logger.info("批量插入成功，操作执行时间："+ (System.currentTimeMillis() -time1));
    	}catch(Exception e){
    		try{
    			sessionBatch.rollback();
    		}catch(Exception ex){
    			logger.warn(ex);
    		}
    		throw e;
    	}finally{
    		try{
    			sessionBatch.close();
    		}catch(Exception ex){
    			logger.warn(ex);
    		}
    	}
     }
    
    /**
     * 从数据源获取connection
     * 注意使用后需调用 conn.close()或DButil.close(conn) 手动关闭！
     */
    public Connection getConnection() throws SQLException{
    	return dataSource.getConnection();
    }
    
    /**
     * 本项目使用的是Spring代理的connection，执行connection时，实际会执行将connection放到连接池的操作
     * @param conn
     * @throws Exception
     */
    public static void close(Connection conn){
    	if(conn != null){
    		try{
    			conn.close();
    		}catch (Exception e) {
				logger.error("关闭连接池失败",e);
			}
    	}
    }
    
    /**
     * 释放Statement操作
     * @param psmt
     */
    public static void close(Statement smt){
    	if (smt != null) {
    		try {
    	    	smt.close();
    		}
    	    catch (SQLException e) {
    	    	logger.error("释放PreparedStatement失败",e);
    	    }
    	}
	}
    
    /**
     * 释放ResultSet操作
     * @param rs
     */
    public static void close(ResultSet rs) {
    	if (rs != null) {
    		try {
    			rs.close();
    		}
    		catch (SQLException e) {
    			logger.error("释放ResultSet失败",e);
    		}
    	}
	}
	
    
	public static void main(String[] args) throws Exception{
		BaseDaoImpl b = new BaseDaoImpl();
		b.query("","");
		b.query("",11);
	}
}
